﻿CREATE view [dbo].[Models] as

select distinct (select top 1 Manufacturer from devicedom b where a.ManufacturerUrl=b.ManufacturerUrl) as Manufacturer,ManufacturerUrl,(case when ModelName='' then 'unknown' else ModelName end) as ModelName,count(*) as SchemaCount
from devicedom a
where Manufacturer is not null
and ModelName is not null
and Manufacturer!=''
and domtype='d'
group by ManufacturerUrl,ModelName

union

select 'unknown' as Manufacturer,'' as ManufacturerUrl,(case when ModelName='' then 'unknown' else ModelName end) as ModelName,count(*) as SchemaCount
from devicedom
where Manufacturer=''
and domtype='d'
group by Manufacturer,ModelName
